import pymysql
import pandas as pd
import asyncio
import time
import datetime
import traceback
import os
import sys
import numpy as np
import pytz
# print(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
# # 添加导包路径
# sys.path.append(os.path.join(os.path.split(os.path.split(sys.path[0])[0])[0], "bin"))
sys.path.append(r"D:\my_work\ac_lingxing_api\ac_lingxing_api")
from multiprocessing import  Process, Queue, Pool
from bin.openapi import OpenApiBase
from sqlalchemy import create_engine
from itertools import product
from itertools import islice
from lxml import etree
import re
import demoji



class tools:
    
    @staticmethod
    def chunked_iterable(iterable, size):
        it = iter(iterable)
        return iter(lambda: tuple(islice(it, size)), ())
    
    
    @staticmethod
    def get_engine():
        engine = create_engine('mysql+pymysql://user_rpa:123456@192.168.8.10:3306/rpa?charset=utf8mb4')
        return engine
    
    @staticmethod
    def get_app_token():
        engine = tools.get_engine()
        res = pd.read_sql("select appId, appSecret from lx_app_token", con=engine)
        app_id_lst = []
        app_secret_lst = []
        for i, data in res.iterrows():
            app_id_lst.append(data['appId'])
            app_secret_lst.append(data['appSecret'])
        return app_id_lst, app_secret_lst

    @staticmethod
    def get_webmail_uuid():
        query = "select webmail_uuid, email from lx_cus_service where is_cus_message = 1 and is_get_detail = 0"
        engine = tools.get_engine()
        query_df = pd.read_sql(query, con=engine)
        return query_df['webmail_uuid'].to_list(), query_df['email'].to_list()

    @staticmethod
    def update_detail(webmail_uuid_str):
        query = f"update lx_cus_service set is_get_detail = 1 where webmail_uuid in ({webmail_uuid_str})"
        conn = pymysql.connect(user='user_rpa', password='123456', host='192.168.8.10', database='rpa')
        cur = conn.cursor()
        cur.execute(query)
        conn.commit()
        cur.close()
        conn.close()
        
    @staticmethod
    def is_update_state_handler(row_data):
        if row_data['from_address'] == "donotreply@amazon.com" or "@marketplace.amazon." in row_data['to_address_all'] :
            return 2
        else:
            return 0
        
    @staticmethod
    def html_text_asin(row_data):
        text_html = row_data['text_html']
        from_address = row_data['from_address']
        element = etree.HTML(text_html)
        try:
            data_asin = re.findall("B0[0-9A-Z]{8}", text_html)
            if len(data_asin)>0:
                return data_asin[0]
        except:
            return ""
        
    @staticmethod
    def html_text_order(row_data):
        text_html = row_data['text_html']
        from_address = row_data['from_address']
        # print(row_data['webmail_uuid'])
        element = etree.HTML(text_html)
        try:
            if from_address == "donotreply@amazon.com":
                order = element.xpath('//*[@id="bodyCell"]/table[2]/tbody/tr/td/table[1]/tbody/tr/td/table/tbody/tr/td/table/tbody/tr/td/p[4]')[0].text.strip()
            else:
                order = element.xpath('//*[@id="bodyCell"]/table[2]/tbody/tr/td/table[1]/tbody/tr/td/table/tbody/tr/td/table/tbody/tr/td/p[2]')[0].text.strip()
            pattern = re.compile(r'[0-9]+-[0-9]+-[0-9]+')
            order_num = pattern.findall(order)[0]
        except:
            order_num = ""
        return order_num
    @staticmethod
    def html_text_message(row_data):
        text_html = row_data['text_html']
        from_address = row_data['from_address']
        element = etree.HTML(text_html)
        if text_html == "":
            message = row_data['text_plain']
        else:
            try:
                
                if from_address == "donotreply@amazon.com":
                    message = element.xpath('//pre')[0].text.strip()
                else:
                    message = element.xpath('//pre')[0].text.strip()
            except:
                print(row_data['webmail_uuid'])
                try:
                    message_ele = element.xpath('//*[@id="bodyCell"]/table[2]/tbody/tr/td/table[1]/tbody/tr/td/table/tbody/tr/td/table/tbody/tr/td/p')[5]
                except:
                    
                    try:
                        message_ele = element.xpath("//div[@dir='auto']")[0]
                    except:
                        try:
                            message_ele = element.xpath('//*[@id="bodyCell"]/table[2]/tbody/tr/td/table[1]/tbody/tr/td/table/tbody/tr/td/table/tbody/tr/td/table[2]/tbody/tr/th/p[1]')[0]
                        except:
                            try:
                                message_ele = element.xpath('/html/body')[0]
                            except:
                                print("报错ID", row_data['webmail_uuid'])
                                print(traceback.format_exc())
                            
                        
                message = etree.tostring(message_ele, encoding='unicode', method='html').replace("<p>", "").replace("<br>", "").replace("</p>", "").strip()
        return message

class lx_api:

    def __init__(self, app_id, app_secret):
        
        self.op_api = OpenApiBase("https://openapi.lingxing.com", app_id, app_secret)
        self.access_token = ""
        self.refresh_token = ""
        self.engine = tools.get_engine()
        self.table_name = "lx_cus_service_detail"
        self.url = "/erp/sc/data/mail/detail"
        

    async def generate_access_token(self):
        resp = await self.op_api.generate_access_token()
        self.access_token = resp.access_token
        self.refresh_token = resp.refresh_token
        assert resp.access_token
        # return resp.dict()
    
    async def refresh_token_handler(self):
        resp = await self.op_api.refresh_token(self.refresh_token)
        self.refresh_token =  resp.refresh_token
        self.access_token = resp.access_token
        assert resp.access_token

    async def check_token(self, data):
        if data['code'] == 0:
            return True
        elif data['code'] in [2001005, 2001003, 2001008, 2001009]:
            await self.generate_access_token()
            return False
        else:
            print(data['code'])
            return False
    
    async def get_email_data(self, webmail_uuid, email):
        req_body = {
            "webmail_uuid":webmail_uuid
        }
        res_lst = []
        while 1:
            try:
                res = await self.op_api.request(self.access_token, self.url, "POST", req_body=req_body)
            except:
                print("异常:", traceback.format_exc())
                time.sleep(1)
                continue
            resp = res.dict()
            if not await self.check_token(resp):
                continue
            if len(resp['data']) != 0:
                res_lst = res_lst + [resp['data']]
            break
        if len(res_lst) == 0:
            print("无数据")
            return []
        res_df = pd.DataFrame(res_lst)
        res_df['update_time'] = pd.to_datetime(datetime.datetime.now())
        res_df['text_html'] = res_df.apply(lambda x: demoji.replace(x['text_html'], ""), axis=1)
        res_df['text_plain'] = res_df.apply(lambda x: demoji.replace(x['text_plain'], ""), axis=1)
        res_df = res_df.drop(['attachments'], axis=1)
        res_df['message'] = res_df.apply(tools.html_text_message, axis=1)
        res_df['order_num'] = res_df.apply(tools.html_text_order, axis=1)
        res_df['detail_asin'] = res_df.apply(tools.html_text_asin, axis=1)
        
        res_df['is_update_state'] = res_df.apply(tools.is_update_state_handler, axis=1)
        res_df['email'] = email
        


        if len(res_df)>0:
            res_df.to_sql(self.table_name, con=self.engine, if_exists="append", index=False)
            webmail_uuid_str = ",".join(["'" + webmail_uuid + "'" for webmail_uuid in res_df['webmail_uuid'].to_list()])
            
            tools.update_detail(webmail_uuid_str)
            print(f"{webmail_uuid} 数据已插入")
        else:
            print("无数据")
    async def hanlder(self, semaphore, webmail_uuid, email):
        async with semaphore:
            await self.get_email_data(webmail_uuid, email)
            
        
    async def main(self, webmail_uuid_lst, email_lst):
        semaphore = asyncio.Semaphore(3)
        await self.generate_access_token()
        tasks = []
        for webmail_uuid, email in zip(webmail_uuid_lst, email_lst):

            tasks.append(asyncio.create_task(self.hanlder(semaphore, webmail_uuid, email)))

        # print(tasks)
        result = await asyncio.gather(*tasks)
            
        
        

def run(app_id, app_secret, webmail_uuid_lst, email_lst):
    
    lx_app = lx_api(app_id, app_secret)
    
    asyncio.run(lx_app.main(webmail_uuid_lst, email_lst))
    
        

def main():
    num = 1
    app_id_lst, app_secret_lst = tools.get_app_token()
    webmail_uuid_lst_all, email_lst_all = tools.get_webmail_uuid()
    webmail_uuid_lst_all = np.array_split(webmail_uuid_lst_all, num)
    email_lst_all = np.array_split(email_lst_all, num)



    pool = Pool(num)
    for i in range(num):
        pool.apply_async(func=run, args=(app_id_lst[i], app_secret_lst[i], webmail_uuid_lst_all[i], email_lst_all[i]))
    pool.close()
    pool.join()
if __name__ == "__main__":  
    main()